# -*- coding: utf-8 -*-

from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class DBHighGoManager(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def define_col(self):
        """
        定义psql 插入字段类型
        :return:
        """
        id = self.get_incre_time()
        col = {
            "id": "serial,",
            "c_02_integer_col": "integer,",
            "c_03_smallint_col": "smallint,",
            "c_04_bigint_col": "bigint,",
            "c_05_double_col": "numeric(9,2),",
            "c_06_float_col": "numeric(9,2),",
            "c_07_decimal_col": "numeric(7,2),",
            "c_08_date_col": "date,",
            "c_09_time_col": "time,",
            "c_10_timestamp_col": "timestamp,",
            "c_11_char_col": "varchar(50),",
            "": "CONSTRAINT cdc_highgo_dml_t1_pk PRIMARY KEY (id))"
        }
        return col

    def define_ddl(self, db, schema, table):
        """
        拼接oracle ddl语句
        :param table:
        :return:
        """
        col = self.define_col()
        ddl_sql = 'CREATE TABLE {}.{}.{} ( \r\n'.format(db, schema, table)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def general_col_colue(self, db, schema, table):
        """
        生成 oracle dml
        :param table:
        :return:
        """
        c_02_integer_col = self.get_random(-2147483648, 2147483647)
        c_03_smallint_col = self.get_random(-32768, 32767)
        c_04_bigint_col = self.get_random(-9223372036854775808, 9223372036854775807)
        c_05_double_col = self.get_random_float(-500, 500)
        c_06_float_col = self.get_random_float(-500, 500)
        c_07_decimal_col = self.get_random_float(-32768, 32767)
        c_09_time_col = "{}".format(self.get_random_time())
        c_11_char_col = self.get_random_string(30)

        sql = "insert into {}.{}.{} (c_02_integer_col, c_03_smallint_col, c_04_bigint_col, c_05_double_col, c_06_float_col," \
              "c_07_decimal_col, c_09_time_col, c_11_char_col) " \
              "values ({},{},{},{},{},{},'{}','{}')" \
              "".format(db, schema, table,  c_02_integer_col, c_03_smallint_col, c_04_bigint_col,
                        c_05_double_col, c_06_float_col, c_07_decimal_col, c_09_time_col, c_11_char_col
                        )
        return sql
    def execute_update_data(self, db, schema, table):
        sql1 = (("select attname AS column_name, atttypid::regtype AS data_type, attnotnull AS is_not_null FROM "
                 "pg_attribute WHERE attrelid = '{}.{}.{}'::regclass AND attnum > 0 AND NOT attisdropped;").
                format(db, schema, table))

        res = self.exec_highGo_sql(db, sql1)
        if res['message'] != "Success":
            return False

        columns = [row[0] for row in res['data'] if row[0] != 'id']

        get_ids_sql = "select id FROM {}.{}.{} ORDER BY id DESC LIMIT 3".format(db, schema, table)
        ids_res = self.exec_highGo_sql(db, get_ids_sql)
        if ids_res['message'] != "Success":
            return False

        ids = [row[0] for row in ids_res['data']]

        for id in ids:
            updates = []
            for col in columns:
                if col == 'c_02_integer_col':
                    updates.append("{} = {}".format(col, self.get_random(-2147483648, 2147483647)))
                elif col == 'c_03_smallint_col':
                    updates.append("{} = {}".format(col, self.get_random(-32768, 32767)))
                elif col == 'c_04_bigint_col':
                    updates.append("{} = {}".format(col, self.get_random(-9223372036854775808, 9223372036854775807)))
                elif col == 'c_05_double_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_06_float_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_07_decimal_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-32768, 32767)))
                elif col == 'c_09_time_col':
                    updates.append("{} = '{}'".format(col, self.get_random_time()))
                elif col == 'c_11_char_col':
                    updates.append("{} = '{}'".format(col, self.get_random_string(30)))

            update_sql = "update {}.{}.{} SET {} WHERE id = {}".format(db, schema, table, ", ".
                                                                       join(updates), id)
            update_res = self.exec_highGo_sql(db, update_sql)
            if update_res['message'] != "Success":
                return False

        return True

    def execute_deleted_data(self, db, schema, table):
        sql = ("delete FROM {}.{}.{} WHERE id IN (SELECT id FROM (SELECT id FROM {}.{}.{} ORDER BY id DESC LIMIT 2)"
               " AS subquery);".format(db, schema, table, db, schema, table))
        res = self.exec_highGo_sql(db, sql)
        if res['message'] == "Success":
            return True
        else:
            return False

    def define_dml(self, db, schema, table, lines):
        """
        批量生成 oracle dml 语句
        :param table:
        :param lines:
        :return:
        """
        insert_sql = ""
        while lines > 0:
            tmp = self.general_col_colue(db, schema, table)
            insert_sql = insert_sql + tmp + ";\r\n"
            lines = lines - 1
        print(insert_sql)
        return insert_sql

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        if is_recreate:
            ddl_sql = self.define_ddl(db, table)
            self.exec_highGo_sql(db, "drop table {}".format(table))
            self.exec_highGo_sql(db, ddl_sql)
        insert_sql = self.define_dml(db, table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_highGo_sql(db, line)

    def set_combkey(self, db, table):
        line = ("SELECT column_name FROM information_schema.columns WHERE table_name = {} ORDER BY ordinal_position "
                "LIMIT 1").format(table)
        print(line)
        line = "show server_version"
        columns = self.exec_highGo_sql(db, line)
        print(columns)
        column1 = columns[0][0]
        column2 = columns[1][0]
        print(column1, column2)

    def set_slot(self, db, schame, slot):
        line = "SELECT pg_drop_replication_slot('{}')".format(slot)
        self.exec_highGo_sql(db, line)

    def get_count(self, db, schame, table):
        sql = "select count(1) as cot from {}.{}.{}".format(db, schame, table)
        res = self.exec_highGo_sql(db, sql)
        print(res)
        if res['message'] == "Success":
            res_num = res['data'][0][0]
            return res_num
        else:
            return res

    def execute_drop_table(self, db, schema, table):
        sql = "drop table {}.{}.{}".format(db, schema, table)
        res = self.exec_highGo_sql(db, sql)
        return self.set_return_massage(res)

    def cdc_set_on(self, db, schame, table):
        sql = "ALTER TABLE {}.{}.{}  REPLICA IDENTITY FULL;".format(db, schame, table)
        return self.exec_highGo_sql(db, sql)

    def get_db_data(self, db, schame, table):
        sql = "select * from {}.{}.{} order by id desc limit 10".format(db, schame, table)
        res = self.exec_dameng_sql(db, sql)
        return self.set_return_massage(res, only_data="yes")


if __name__ == '__main__':
    pass
    # # 创建
    a=DBHighGoManager()
    sql = a.define_ddl("qa_source", "public", "cdc_highgo_dml_t2")
    a.exec_highGo_sql("qa_source", sql)

    # # 写入
    # sql = a.define_dml("qa_source", "public", "cdc_highgo_dml_t2", 2)
    # a.exec_highGo_sql("qa_source", sql)
    #
    # # 更新数据
    # a.execute_update_data("qa_source", "public", "cdc_highgo_dml_t2")
    #
    # 删除数据
    # a.execute_deleted_data("qa_source", "public", "cdc_highgo_dml_t2")
    #
    #
    # # 删除表
    # a.execute_drop_table("qa_source", "public", "cdc_highgo_dml_t2")
